﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StockManagement.Model;
using System.Data.SqlClient;
using System.Data;
using StockManagement.ViewModels;
using StockManagement.Models;

namespace StockManagement.Helper
{    
    public static class DatabaseHelper
    {
        public static string CONNECTIONSTRING = @"Data Source=LocalHost\SQLEXPRESS;Initial Catalog=StoreManagement;Integrated Security=SSPI";

        public static SqlConnection GetConnection()
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(CONNECTIONSTRING);
                connection.Open();
            }
            catch (Exception ex)
            {

                if (connection != null)
                {
                    if (ex.Message != string.Empty)
                    { }
                    connection.Dispose();
                }
            }
            return connection;
        }

        public static void AddParameter(this SqlCommand command, string parameterName, object parameterValue, SqlDbType parameterSqlType)
        {
            if (!parameterName.StartsWith("@"))
            {
                parameterName = "@" + parameterName;
            }
            command.Parameters.Add(parameterName, parameterSqlType, 200);
            command.Parameters[parameterName].Value = parameterValue;
        }

        public static SqlCommand GetCommand(this SqlConnection connection, string procName, CommandType commandType)
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandTimeout = connection.ConnectionTimeout;
            command.CommandType = commandType;
            command.CommandText = procName;
            return command;
        }
    }

    class Adapter
    {
        public static Material GetMaterial(Guid MaterialID)
        {
            Material material;
            string query = @"SELECT MaterialID, Name, MaterialType, MaterialCode, VendorID,Rate,Quantity, Date,Vat,Purchaser FROM Material Where MaterialID ='" + MaterialID+"'";

            // Fill the Set with the data
            using (SqlConnection conn = DatabaseHelper.GetConnection())
            {
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = query;

                    DataSet ds = new DataSet("Material");                    
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    material = new Material((DataRow)ds.Tables[0].Rows[0]);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }
            return material;
        }

        public static bool DeleteMaterial(Guid materialID)
        {
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    //Need to execute stored proc to check if the material is being used by any products
                    int retunvalue = -1;
                    using (SqlCommand command = connection.GetCommand("CheckMaterialUsage", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialID", materialID, SqlDbType.UniqueIdentifier);
                        SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
                        retval.Direction = ParameterDirection.Output;

                       command.ExecuteNonQuery();
                        retunvalue = (int)command.Parameters["@b"].Value;
                    }

                    if (retunvalue == 0)
                    {
                    using (SqlCommand command = connection.GetCommand("DC_Material", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialID", materialID, SqlDbType.UniqueIdentifier);
                        command.ExecuteNonQuery();
                    }
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool AddNewMaterial(Material material)
        {
            try
            {
                // get the connection
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("IC_Material", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialID", material.MaterialID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Name", material.Name, SqlDbType.NVarChar);
                        command.AddParameter("@MaterialType", material.MaterialType.ToString(), SqlDbType.NVarChar);
                        command.AddParameter("@MaterialCode", material.MaterialCode, SqlDbType.NVarChar);
                        command.AddParameter("@VendorID", material.VendorID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Rate", material.Rate, SqlDbType.Int);
                        command.AddParameter("@Quantity", material.Quantity, SqlDbType.Int);
                        command.AddParameter("@Date", DateTime.Now, SqlDbType.DateTime);
                        command.AddParameter("@Vat", material.Vat, SqlDbType.Float);
                        command.AddParameter("@Purchaser", material.Purchaser, SqlDbType.NVarChar);

                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool ValidateMaterialName(string materialName)
        {
            try
            {
                int retunvalue = -1;
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    //Need to execute stored proc to check if the material is being used by any products

                    using (SqlCommand command = connection.GetCommand("IsMaterialNameExists", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialName", materialName, SqlDbType.NVarChar);
                        SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
                        retval.Direction = ParameterDirection.Output;

                        command.ExecuteNonQuery();
                        retunvalue = (int)command.Parameters["@b"].Value;
                    }
                }

                return (retunvalue == 0);
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateEditMaterial(Material material,string code,Guid vendorID,double rate,int quantity,DateTime date,double vat)
        {
            try
            {
                // get the connection
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("UC_Material", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialID", material.MaterialID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Code", code, SqlDbType.NVarChar);
                        command.AddParameter("@VendorID", vendorID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Rate", rate, SqlDbType.Int);
                        command.AddParameter("@Quantity", quantity, SqlDbType.Int);
                        command.AddParameter("@Date", date, SqlDbType.DateTime);
                        command.AddParameter("@Vat", vat, SqlDbType.Float);
                        command.AddParameter("@Purchaser", material.Purchaser, SqlDbType.NVarChar);

                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateDeliverMaterial(Material material,string purchaser)
        {
            try
            {
                // get the connection
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("UC_Material", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@MaterialID", material.MaterialID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Code", material.MaterialCode, SqlDbType.NVarChar);
                        command.AddParameter("@VendorID", material.VendorID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@Rate", material.Rate, SqlDbType.Int);
                        command.AddParameter("@Quantity", material.Quantity, SqlDbType.Int);
                        command.AddParameter("@Date", material.Date, SqlDbType.DateTime);
                        command.AddParameter("@Vat", material.Vat, SqlDbType.Float);
                        command.AddParameter("@Purchaser", purchaser, SqlDbType.NVarChar);

                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        public static bool AddNewVendor(Vendor vendor)
        {
            try
            {
                // get the connection
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("IC_Vendor", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@VendorID", vendor.VendorID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@FirstName", vendor.FirstName, SqlDbType.NVarChar);
                        command.AddParameter("@LastName", vendor.LastName, SqlDbType.NVarChar);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool AddNewProduct(Product prod)
        {
            try
            {

                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                    try
                    {
                        using (SqlCommand command = connection.GetCommand("IC_Product", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;
                            command.AddParameter("@ProductID", prod.ProductID, SqlDbType.UniqueIdentifier);
                            command.AddParameter("@ProductName", prod.Name, SqlDbType.NVarChar);
                            command.AddParameter("@ProductCode", prod.ProductCode, SqlDbType.NVarChar);
                            command.AddParameter("@Type", (short)prod.ProductType, SqlDbType.Int);
                            command.AddParameter("@Status", (short)prod.Status, SqlDbType.Int);
                            command.ExecuteNonQuery();
                        }

                        using (SqlCommand command = connection.GetCommand("IC_ProductMaterial", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;

                            command.Parameters.Add("@ProductID", SqlDbType.UniqueIdentifier);
                            command.Parameters.Add("@MaterialID", SqlDbType.UniqueIdentifier);
                            command.Parameters.Add("@Quantity", SqlDbType.Int);
                            SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
                            retval.Direction = ParameterDirection.Output;

                            foreach (ProductMaterial prodMaterial in prod.MaterialsQuantity)
                            {
                                command.Parameters["@ProductID"].Value = prod.ProductID;
                                command.Parameters["@MaterialID"].Value =prodMaterial.MaterialID;
                                command.Parameters["@Quantity"].Value = prodMaterial.MaterialQuantity;
                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }

        }

        public static bool ConsumeProduct(Product prod)
        {
            try
            {

                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                    try
                    {
                        using (SqlCommand command = connection.GetCommand("ConsumeProduct", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;

                            foreach (ProductMaterial prodMaterial in prod.MaterialsQuantity)
                            {
                                command.AddParameter("@ProductID", prod.ProductID, SqlDbType.UniqueIdentifier);
                                command.AddParameter("@MaterialID", prodMaterial.MaterialID, SqlDbType.UniqueIdentifier);
                                command.AddParameter("@Quantity", prodMaterial.MaterialQuantity, SqlDbType.Int);
                                SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
                                retval.Direction = ParameterDirection.Output;

                                command.ExecuteNonQuery();
                                int retunvalue = (int)command.Parameters["@b"].Value;
                                if (retunvalue == 0)
                                {
                                    transaction.Rollback();
                                    return false;
                                }
                            }
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool DeleteProduct(Guid productID)
        {
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("DC_Product", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@ProductID", productID, SqlDbType.UniqueIdentifier);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateEditProduct(Product product, string prodName, ProductMaterialList lstProdMaterial)
        {
            //Update Name,ProductMaterials, date, status
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                    try
                    {
                        using (SqlCommand command = connection.GetCommand("UC_Product", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;
                            command.AddParameter("@ProductID", product.ProductID, SqlDbType.UniqueIdentifier);
                            command.AddParameter("@ProductName", prodName, SqlDbType.NVarChar);
                            command.AddParameter("@Status", (short)product.Status, SqlDbType.Int);
                            command.AddParameter("@Date",DateTime.Now, SqlDbType.DateTime);
                            command.AddParameter("@Purchaser", product.Purchaser, SqlDbType.NVarChar);
                            command.AddParameter("@Consumer", product.Consumer, SqlDbType.NVarChar);
                            command.AddParameter("@bUpdateMatQuantity", 1, SqlDbType.Int);
                            command.ExecuteNonQuery();
                        }

                        int retValue = -1;
                        using (SqlCommand command = connection.GetCommand("IC_ProductMaterial", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;
                            command.Parameters.Add("@ProductID", SqlDbType.UniqueIdentifier);
                            command.Parameters.Add("@MaterialID", SqlDbType.UniqueIdentifier);
                            command.Parameters.Add("@Quantity", SqlDbType.Int);
                            SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
                            retval.Direction = ParameterDirection.ReturnValue;

                            foreach (ProductMaterial prodMaterial in product.MaterialsQuantity)
                            {
                                command.Parameters["@ProductID"].Value = product.ProductID;
                                command.Parameters["@MaterialID"].Value = prodMaterial.MaterialID;
                                command.Parameters["@Quantity"].Value = prodMaterial.MaterialQuantity;
                                retValue = command.ExecuteNonQuery();
                                int retunvalue = (int)command.Parameters["@b"].Value;
                                if (retValue == -1)
                                    throw new Exception();
                            }
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateConsumeProduct(Product product, string consumer)
        {
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                    try
                    {

                        using (SqlCommand command = connection.GetCommand("UC_Product", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;
                            command.AddParameter("@ProductID", product.ProductID, SqlDbType.UniqueIdentifier);
                            command.AddParameter("@ProductName", product.Name, SqlDbType.NVarChar);
                            command.AddParameter("@Status", (short)ProductStatus.Inward_InProgress, SqlDbType.Int);
                            command.AddParameter("@Date", DateTime.Now, SqlDbType.DateTime);
                            command.AddParameter("@Purchaser", product.Purchaser, SqlDbType.NVarChar);
                            command.AddParameter("@Consumer", consumer, SqlDbType.NVarChar);
                            command.AddParameter("@bUpdateMatQuantity", 0, SqlDbType.Int);
                            command.ExecuteNonQuery();
                        }

                        using (SqlCommand command = connection.GetCommand("UC_MaterialQuantity", CommandType.StoredProcedure))
                        {
                            command.Connection = connection;
                            command.Transaction = transaction;
                            command.AddParameter("@MaterialID", Guid.Empty, SqlDbType.UniqueIdentifier);
                            command.AddParameter("@Quantity", 0, SqlDbType.Int);

                            foreach (ProductMaterial prodMaterial in product.MaterialsQuantity)
                            {
                                command.Parameters["@MaterialID"].Value = prodMaterial.MaterialID;
                                command.Parameters["@Quantity"].Value = prodMaterial.MaterialQuantity;
                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateConsumeCompleteProduct(Product product)
        {
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("UC_Product", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@ProductID", product.ProductID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@ProductName", product.Name, SqlDbType.NVarChar);
                        command.AddParameter("@Status", (short)ProductStatus.Outward_ToBeDeliverd, SqlDbType.Int);
                        command.AddParameter("@Date", DateTime.Now, SqlDbType.DateTime);
                        command.AddParameter("@Purchaser", product.Purchaser, SqlDbType.NVarChar);
                        command.AddParameter("@Consumer",product.Consumer, SqlDbType.NVarChar);
                        command.AddParameter("@bUpdateMatQuantity", 0, SqlDbType.Int);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool UpdateDeliverProduct(Product product, string purchaser)
        {
            try
            {
                using (SqlConnection connection = DatabaseHelper.GetConnection())
                {
                    using (SqlCommand command = connection.GetCommand("UC_Product", CommandType.StoredProcedure))
                    {
                        command.AddParameter("@ProductID", product.ProductID, SqlDbType.UniqueIdentifier);
                        command.AddParameter("@ProductName", product.Name, SqlDbType.NVarChar);
                        command.AddParameter("@Status", (short)ProductStatus.Delivered, SqlDbType.Int);
                        command.AddParameter("@Date", DateTime.Now, SqlDbType.DateTime);
                        command.AddParameter("@Purchaser", purchaser, SqlDbType.NVarChar);
                        command.AddParameter("@Consumer", product.Consumer, SqlDbType.NVarChar);
                        command.AddParameter("@bUpdateMatQuantity", 0, SqlDbType.Int);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        //public void AddProduct(Product prod)
        //{
        //    // get the connection
        //    using (SqlConnection connection = DatabaseHelper.GetConnection())
        //    {
        //        using (SqlCommand command = connection.GetCommand("UC_Material", CommandType.StoredProcedure))
        //        {
        //            command.AddParameter("@ProductID", prod.ProductID, SqlDbType.UniqueIdentifier);
        //            command.AddParameter("@ProductName", prod.Name, SqlDbType.NVarChar);
        //            command.AddParameter("@ProductCode", prod.ProductCode, SqlDbType.NVarChar);
        //            command.AddParameter("@Type", (short)prod.ProductType, SqlDbType.Int);
        //            command.AddParameter("@Status", (short)prod.Status, SqlDbType.Int);
        //            command.ExecuteNonQuery();
        //        }
        //    }
        //}

        //public int AddNewProductMaterial(Product prod, ProductMaterial prodMaterial)
        //{
        //    int retValue = -1;
        //    using (SqlConnection connection = DatabaseHelper.GetConnection())
        //    {
        //        using (SqlCommand command = connection.GetCommand("IC_ProductMaterial", CommandType.StoredProcedure))
        //        {
        //            command.AddParameter("@ProductID", prod.ProductID, SqlDbType.UniqueIdentifier);
        //            command.AddParameter("@MaterialID", prodMaterial.MaterialID, SqlDbType.UniqueIdentifier);
        //            command.AddParameter("@Quantity", prodMaterial.MaterialQuantity, SqlDbType.Int);
        //            SqlParameter retval = command.Parameters.Add("@b", SqlDbType.Int);
        //            retval.Direction = ParameterDirection.ReturnValue;

        //            retValue = command.ExecuteNonQuery();
        //            int retunvalue = (int)command.Parameters["@b"].Value;

        //        }





        //        return retValue;
        //    }
        //}

    }
}
